﻿using System;
using System.IO;
using System.Xml;
using System.Text;
using System.Collections.Generic;
using SdmxMl.Structure;
using SdmxMl.Common;
using SdmxMl.GenericMetadata;
using SdmxMl.Manager;

using System.Data.OleDb;


namespace SilcQuest.Grid
{
    public class GridImport
    {
        const string DB_CONN_STRING = "provider = microsoft.jet.oledb.4.0;data source = ";
        private OleDbConnection connection;
        private OleDbCommand command;
        private SmManager sm;
        private List<string> listVar;

        private MetadataSetType metaSet;
        private MetadataSetType metaSetTech;
        private MetadataSetType metaSuiviTech;
        private MetadataSetType metaSuiviGrid;
        private MetadataStructureDefinitionType msd = null;
        private ReportStructureType reportGrid = null;
        private ReportStructureType reportTech = null;
        private List<string> listNoText;

        public GridImport(SmManager smMgr)
        {
            sm = smMgr;

            try
            {
                // Fetch targeted variables
                listVar = new List<string>(128);
                using (StreamReader sr = new StreamReader(@"c:\doc\SILC2012_QUESTIONNAIRES_GRILLE\GridVar.csv", Encoding.Default))
                {
                    string var = sr.ReadLine();
                    while (var != null)
                    {
                        listVar.Add(var);
                        var = sr.ReadLine();
                    }
                }

                // Open MIS Access DB
                string coStr = DB_CONN_STRING;
                coStr += @"c:\doc\SILC2012_QUESTIONNAIRES_GRILLE\SILC2012_QUESTIONNAIRES_GRILLE.mdb";
                connection = new OleDbConnection(coStr);
                connection.Open();
                command = new OleDbCommand();
                command.Connection = connection;

            }
            catch
            {
                throw new ApplicationException("Unable to open MIS Access DB");
            }
        }

        public void GetVar()
        {
            ConstructMetasetArtefact();

            listNoText = new List<string>();
            foreach (string var in listVar)
                GetAssociatedTexts(var);

            XmlDocument xDoc = sm.GetMetasetAsDocument(metaSet.FullIdent);
            ReferenceManager.TreeMgr.LoadSdmxArtefact(null, true, xDoc);

        }

        public void Close()
        {
            connection.Close();
        }

        private void ConstructMetasetArtefact()
        {
            metaSetTech = new MetadataSetType();

            metaSetTech.Id = "GMD_TECH_GRID";
            metaSetTech.AgencyId = "CH1_SILC";
            metaSetTech.Version = "2012.0";
            metaSetTech.Name.UpdateDefaultText("SILC - " + "techniqual question Grid (filter only) 2012");

            // Annotate as CATI for HTML display in editor
            Annotation ann = new Annotation("CATI_X");
            ann.Text.UpdateForLanguage("en", "computer-assisted telephone interview");
            ann.Text.UpdateForLanguage("fr", "application ITAO (interview téléphonique assistée par ordinateur)");
            ann.Text.UpdateForLanguage("de", "computergestütztem Fragenprogramm in telefonischen Interviews.");
            metaSetTech.AnnotationList.Add(ann);


            // Refered MSD FullId
            metaSetTech.MetadataStructureRef.Id = "MD_SILC_CATI_QUEST";
            metaSetTech.MetadataStructureRef.AgencyId = "CH1_SILC";
            metaSetTech.MetadataStructureRef.Version = "1.0";

            // Refered report
            metaSetTech.ReportRef = "R_SILC_TECH";
            sm.PutArtefact(SdmxArtefactType.MetadataSet, metaSetTech);


            metaSet = new MetadataSetType();

            metaSet.Id = "GRID";
            metaSet.AgencyId = "CH1_SILC";
            metaSet.Version = "2012.0";
            metaSet.Name.UpdateDefaultText("SILC - " + "question Grid 2012");

            // Annotate as CATI for HTML display in editor
            ann = new Annotation("CATI_X");
            ann.Text.UpdateForLanguage("en", "computer-assisted telephone interview");
            ann.Text.UpdateForLanguage("fr", "application ITAO (interview téléphonique assistée par ordinateur)");
            ann.Text.UpdateForLanguage("de", "computergestütztem Fragenprogramm in telefonischen Interviews.");
            metaSet.AnnotationList.Add(ann);


            // Refered MSD FullId
            metaSet.MetadataStructureRef.Id = "MD_SILC_CATI_QUEST";
            metaSet.MetadataStructureRef.AgencyId = "CH1_SILC";
            metaSet.MetadataStructureRef.Version = "1.0";

            // Refered report
            metaSet.ReportRef = "R_SILC_CATI";
            sm.PutArtefact(SdmxArtefactType.MetadataSet, metaSet);

            // SUIVI HISTORY
            metaSuiviGrid = new MetadataSetType();

            metaSuiviGrid.Id = "GMD_SUIVI_GRID";
            metaSuiviGrid.AgencyId = "CH1_SILC";
            metaSuiviGrid.Version = "1.0";
            metaSuiviGrid.Name.UpdateDefaultText("SILC - " + "Historical tracing for Grid");

            // Refered MSD FullId
            metaSuiviGrid.MetadataStructureRef.Id = "MSD_SUIVI";
            metaSuiviGrid.MetadataStructureRef.AgencyId = "CH1_SILC";
            metaSuiviGrid.MetadataStructureRef.Version = "1.0";

            // Refered report
            metaSuiviGrid.ReportRef = "R_SUIVI";
            sm.PutArtefact(SdmxArtefactType.MetadataSet, metaSuiviGrid);
            
            // SUIVI TECHNICAL HISTORY
            metaSuiviTech = new MetadataSetType();

            metaSuiviTech.Id = "GMD_SUIVI_TECH_GRID";
            metaSuiviTech.AgencyId = "CH1_SILC";
            metaSuiviTech.Version = "1.0";
            metaSuiviTech.Name.UpdateDefaultText("SILC - " + "Historical tracing for Grid Technical");

            // Refered MSD FullId
            metaSuiviTech.MetadataStructureRef.Id = "MSD_SUIVI";
            metaSuiviTech.MetadataStructureRef.AgencyId = "CH1_SILC";
            metaSuiviTech.MetadataStructureRef.Version = "1.0";

            // Refered report
            metaSuiviTech.ReportRef = "R_SUIVI";
            sm.PutArtefact(SdmxArtefactType.MetadataSet, metaSuiviTech);



            // Fetch MSD and reports for Grid and Grid Tech quest
            msd = sm.GetArtefact(SdmxArtefactType.MetadataStructureDefinitions,
                new ArtefactRefBase(metaSet.MetadataStructureRef.FullIdent), true) as MetadataStructureDefinitionType;
            reportGrid = msd.ReportStructureList.LocateItem("R_SILC_CATI");
            reportTech = msd.ReportStructureList.LocateItem("R_SILC_TECH");



        }

        private bool GetAssociatedTexts(string texteId)
        {
            string[] arrLg = { "en", "en", "fr", "de", "it" };
            bool original = false;
            string cmdBase = "SELECT [TYPE VARIABLES], [LABEL VARIABLE D], [LABEL VARIABLE F], [LABEL VARIABLE I], " +
                "[INSTRUCTION D],[INSTRUCTION F],[INSTRUCTION I], " +
                "[QUESTION D],[QUESTION F],[QUESTION I], " +
                "[INTRODUCTION D],[INTRODUCTION F],[INTRODUCTION I] FROM TOTO WHERE VARIABLE_$$='{0}'";
            command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = string.Format(cmdBase, texteId);
            OleDbDataReader reader = command.ExecuteReader();

            if (reader.HasRows == false)
            {
                listNoText.Add(texteId.ToString());
                return false;
            }

            while (reader.Read())
            {
                // Type Variable
                original = false;
                object ob = reader.GetValue(0);
                if (ob != DBNull.Value)
                {
                    original = ob.ToString() =="Original";
                }


                MultiLang mlLabel = GetMultilang(1, reader);

                





                // Now MetaSet
                AttributeValueSetType at = original ? new AttributeValueSetType(msd, reportGrid, metaSet) : new AttributeValueSetType(msd, reportTech, metaSet);

                // The target values
                at.TargetValuesTypeList.SetComponentValue("QUEST_TYPE", "G");
                at.TargetValuesTypeList.SetComponentValue("YEAR", "2012");
                at.TargetValuesTypeList.SetComponentValue("VARNAME", texteId);


                // The reported attributes
             //   SetRepAttValue(at, "WORDER", new MultiLang(wOrder.ToString()));
             //   SetRepAttValue(at, "DOMAINS", new MultiLang(r["Domains"].ToString()));
                SetRepAttValue(at, "LABEL_OPTIONAL", mlLabel);

                //            SetRepAttValue(at, "INFO_INT", new MultiLang(vh.domains));
                if (original)
                {
                    // Fetch additionnal Info
                    MultiLang mlInfo = GetMultilang(4, reader);
                    MultiLang mlQuest = GetMultilang(7, reader);
                    MultiLang mlIntro = GetMultilang(10, reader);

                    SetRepAttValue(at, "INFO_INT", mlInfo);
                    SetRepAttValue(at, "QUESTION", mlQuest);
                    SetRepAttValue(at, "INTRO_QUEST", mlIntro);


                    metaSet.AttributeValueSetList.Add(at);
                }
                else
                    metaSetTech.AttributeValueSetList.Add(at);
            }
            //close the reader 
            reader.Close();

            using (StreamWriter sw = new StreamWriter(@"c:\doc\SILC2012_QUESTIONNAIRES_GRILLE\MissingTextVar.txt", false, Encoding.Default))
            {
                foreach (string var in listNoText)
                    sw.WriteLine(var);
            }
            return original;
        }

        private MultiLang GetMultilang(int ndx, OleDbDataReader reader)
        {
            MultiLang ml = new MultiLang();
            object ob = reader.GetValue(ndx++);
            if (ob != DBNull.Value)
            {
                ml.UpdateForLanguage("de", ob.ToString());
            }
            ob = reader.GetValue(ndx++);
            if (ob != DBNull.Value)
            {
                ml.UpdateForLanguage("fr", ob.ToString());
            }
            ob = reader.GetValue(ndx++);
            if (ob != DBNull.Value)
            {
                ml.UpdateForLanguage("it", ob.ToString());
            }

            return ml;
        }

        private void SetRepAttValue(AttributeValueSetType at, string conceptId, MultiLang ml)
        {
            ReportedAttributeType ra = at.ReportedAttributeList.GetReportedAttributeOfConcept(conceptId);
            if (ra != null)
                ra.Value = ml == null ? new MultiLang() : ml;
        }

    }
}
